package org.kevinth.mail;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.tulkas.util.FileUtil;

/**
 * @author <a href="mailto:tulkas.web@gmail.com">Tulkas</a>
 *
 */
public class MailImport {
	private static Connection conn;
	private static PreparedStatement stmtDomain;

	private static PreparedStatement stmtMail;
	private static PreparedStatement stmtMailSite;
	private static PreparedStatement stmtMailPass;
	private static PreparedStatement stmtCheckMail;
	private static PreparedStatement stmtCheckMailSite;
	private static PreparedStatement stmtCheckMailSiteNoUser;
	private static PreparedStatement stmtCheckMailPass;

	private static PreparedStatement stmtQQSite;
	private static PreparedStatement stmtQQPass;
	private static PreparedStatement stmtCheckQQSite;
	private static PreparedStatement stmtCheckQQSiteNoUser;
	private static PreparedStatement stmtCheckQQPass;

	private static long pos = 0;

	private static String encoding = "gbk";

	private static byte rByte = -1;

	private static int siteId = 3;

	private static int batchSize = 200;

	private static String workDir = "D:/tmp/mail";

	private static Map<String, Integer> domains = new HashMap<String, Integer>();

	private static PrintWriter writer = null;

	private static int mailInserted = 0;
	private static int qqInserted = 0;
	private static int domainInserted = 0;
	private static int siteInserted = 0;
	private static int passInserted = 0;

	private static int mailSkipped = 0;
	private static int conWrong = 0;
	private static int domainSkipped = 0;

	private static void output(String str) {
		System.out.println(str);
		writer.println(str);
		writer.flush();
	}

	private static long loadPosition() {
		try {
			FileReader fr = new FileReader("position.txt");
			String str = FileUtil.readString(fr);
			fr.close();
			return Long.parseLong(str);
		} catch (Exception e) {
			return 0;
		}
	}

	private static void savePosition(long value) throws Exception {
		FileWriter fw = new FileWriter("position.txt");
		PrintWriter w = new PrintWriter(fw);
		w.write(String.valueOf(value));
		w.close();
	}

	private static boolean moveOneFile(String dir, String work)
			throws Exception {
		File[] files = new File(dir).listFiles();
		for (File file : files) {
			if (!file.isDirectory()) {
				return file.renameTo(new File(work + "/" + file.getName()));
			}
		}
		return false;
	}

	private static String checkString(String input) {
		String ret = input.trim();
		if ("".equals(ret))
			ret = null;
		return ret;
	}

	private static void insertOne(String line) throws Exception {
		String[] rawStrs = line.split("\\s");
		List<String> strs = new ArrayList<String>();
		for (String str : rawStrs) {
			String s = checkString(str);
			if (s != null)
				strs.add(s);
		}
		if (strs.size() == 3) {
			insertDB(strs.get(2), strs.get(1), strs.get(0));
		} else {
			conWrong++;
		}
	}

	private static long isQQ(String user, String domain) {
		if ("qq.com".equals(domain)) {
			try {
				long qq = Long.parseLong(user);
				long max = ((long) Integer.MAX_VALUE) * 2;
				if (qq < max)
					return qq;
				else
					return 0;
			} catch (NumberFormatException e) {
				return 0;
			}
		} else {
			return 0;
		}
	}

	private static int insertEmail(String user, String domain) {
		int key = -1;
		Integer domainId = getDomainId(domain);
		if (domainId == null || user == null) {
			conWrong++;
			return -1;
		}

		try {
			stmtCheckMail.setString(1, user);
			stmtCheckMail.setInt(2, domainId);
			ResultSet rs = stmtCheckMail.executeQuery();
			while (rs.next()) {
				key = rs.getInt(1);
			}
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		if (key == -1) {
			try {
				stmtMail.setString(1, user);
				stmtMail.setInt(2, domainId);
				int rows = stmtMail.executeUpdate();
				if (rows > 0) {
					mailInserted++;
					ResultSet rs = stmtMail.getGeneratedKeys();
					while (rs.next()) {
						key = rs.getInt(1);
					}
				} else {
					conWrong++;
				}
			} catch (SQLException e) {
				output("ERROR: " + e.getMessage());
				conWrong++;
			}
		} else {
			mailSkipped++;
		}
		return key;
	}

	private static void insertSite(int mailId, String username) {
		boolean ava = false;
		PreparedStatement ps = null;
		try {
			if (username != null) {
				ps = stmtCheckMailSite;
				stmtCheckMailSite.setString(2, username);
			} else {
				ps = stmtCheckMailSiteNoUser;
			}
			ps.setInt(1, mailId);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int i = rs.getInt(1);
				if (i == 0)
					ava = true;
			}
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		if (ava) {
			try {
				stmtMailSite.setInt(1, mailId);
				stmtMailSite.setInt(2, siteId);
				stmtMailSite.setString(3, username);
				int rows = stmtMailSite.executeUpdate();
				if (rows > 0)
					siteInserted++;
			} catch (SQLException e) {
				output("ERROR: " + e.getMessage());
			}
		} else {
		}
	}

	private static void insertPass(int mailId, String password) {
		boolean ava = false;
		try {
			stmtCheckMailPass.setInt(1, mailId);
			stmtCheckMailPass.setString(2, password);
			ResultSet rs = stmtCheckMailPass.executeQuery();
			while (rs.next()) {
				int i = rs.getInt(1);
				if (i == 0)
					ava = true;
			}
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		if (ava) {
			try {
				stmtMailPass.setInt(1, mailId);
				stmtMailPass.setString(2, password);
				int rows = stmtMailPass.executeUpdate();
				if (rows > 0)
					passInserted++;
			} catch (SQLException e) {
				output("ERROR: " + e.getMessage());
			}
		} else {
		}
	}

	private static void insertQQPass(long qq, String password) {
		boolean ava = false;
		try {
			stmtCheckQQPass.setLong(1, qq);
			stmtCheckQQPass.setString(2, password);
			ResultSet rs = stmtCheckQQPass.executeQuery();
			while (rs.next()) {
				int i = rs.getInt(1);
				if (i == 0)
					ava = true;
			}
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		if (ava) {
			try {
				stmtQQPass.setLong(1, qq);
				stmtQQPass.setString(2, password);
				int rows = stmtQQPass.executeUpdate();
				if (rows > 0)
					passInserted++;
			} catch (SQLException e) {
				output("ERROR: " + e.getMessage());
			}
		} else {
		}
	}

	private static void insertQQSite(long qq, String username) {
		boolean ava = false;
		PreparedStatement ps = null;
		try {
			if (username != null) {
				ps = stmtCheckQQSite;
				stmtCheckQQSite.setString(2, username);
			} else {
				ps = stmtCheckQQSiteNoUser;
			}
			ps.setLong(1, qq);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int i = rs.getInt(1);
				if (i == 0)
					ava = true;
			}
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		if (ava) {
			try {
				stmtQQSite.setLong(1, qq);
				stmtQQSite.setInt(2, siteId);
				stmtQQSite.setString(3, username);
				int rows = stmtQQSite.executeUpdate();
				if (rows > 0)
					siteInserted++;
			} catch (SQLException e) {
				output("ERROR: " + e.getMessage());
			}
		} else {
		}
	}

	private static void insertDB(String mail, String password, String username) {
		try {
			if (!EmailChecker.checkEmail(mail)) {
				String unQuote = EmailChecker.unQuote(mail);
				if (unQuote != null)
					mail = unQuote;
				else {
					conWrong++;
					return;
				}
			}

			if (password == null || "".equals(password)) {
				conWrong++;
				return;
			}

			String domain = EmailChecker.getDomain(mail);
			String user = EmailChecker.getUser(mail);
			if (user != null && user.equals(username)) {
				username = null;
			}
			long qq = isQQ(user, domain);
			if (qq != 0) {
				qqInserted++;
				insertQQPass(qq, password);
				insertQQSite(qq, username);
			} else {
				int key = insertEmail(user, domain);
				insertPass(key, password);
				insertSite(key, username);
			}
		} catch (Exception e) {
			output("ERROR:" + e.getMessage());
		}
	}

	private static String readLine(InputStream is) throws Exception {
		int b = -1;
		List<Byte> list = new ArrayList<Byte>();
		if (rByte != -1)
			list.add(rByte);
		while (true) {
			b = is.read();
			if (b == -1)
				return null;
			else
				pos++;
			if (b != '\r' && b != '\n')
				list.add((byte) b);
			else
				break;
		}

		if (b == -1)
			return null;

		b = '\r';
		while (b == '\n' || b == '\r') {
			b = is.read();
			if (b == -1)
				return null;
			else
				pos++;
		}
		rByte = (byte) b;

		byte[] bytes = new byte[list.size()];
		for (int i = 0; i < bytes.length; i++) {
			bytes[i] = list.get(i);
		}
		return new String(bytes, encoding);
	}

	public static void renewDBConn() throws Exception {
		conn = DriverManager
				.getConnection("jdbc:MySQL://D-SHC-00436924:3306/cn_contact_repo?user=root&password=admin&useUnicode=true&characterEncoding=utf8");
		stmtDomain = conn.prepareStatement(
				"insert into email_domain(domain) values (?)",
				Statement.RETURN_GENERATED_KEYS);
		stmtMail = conn.prepareStatement(
				"insert into email(email_user , domain_id) values (?, ?)",
				Statement.RETURN_GENERATED_KEYS);
		stmtMailSite = conn
				.prepareStatement("insert into email_site(email_id , site_id , site_user) values (?, ?, ?)");
		stmtMailPass = conn
				.prepareStatement("insert into email_password(email_id , password) values (?, ?)");
		stmtCheckMail = conn
				.prepareStatement("select id from email where email_user=? and domain_id=?");
		stmtCheckMailSite = conn
				.prepareStatement("select count(*) from email_site where email_id=? and site_user=? and site_id="
						+ siteId);
		stmtCheckMailSiteNoUser = conn
				.prepareStatement("select count(*) from email_site where email_id=? and site_id="
						+ siteId);
		stmtCheckMailPass = conn
				.prepareStatement("select count(*) from email_password where email_id=? and password=?");

		stmtQQSite = conn
				.prepareStatement("insert qq_site(qq , site_id, site_user) values(? , ? , ?)");
		stmtQQPass = conn
				.prepareStatement("insert qq_password(qq , password) values(? , ?)");
		stmtCheckQQSite = conn
				.prepareStatement("select count(*) from qq_site where qq=? and site_user=? and site_id="
						+ siteId);
		stmtCheckQQSiteNoUser = conn
				.prepareStatement("select count(*) from qq_site where qq=? and site_id="
						+ siteId);
		stmtCheckQQPass = conn
				.prepareStatement("select count(*) from qq_password where qq=? and password=?");

		mailInserted = 0;
		domainInserted = 0;
		siteInserted = 0;
		passInserted = 0;
		qqInserted = 0;

		mailSkipped = 0;
		conWrong = 0;
		domainSkipped = 0;
	}

	public static void closeDBConn() throws Exception {
		if (stmtMail != null)
			stmtMail.close();
		if (stmtMailSite != null)
			stmtMailSite.close();
		if (stmtMailPass != null)
			stmtMailPass.close();
		if (stmtDomain != null)
			stmtDomain.close();
		if (stmtCheckMail != null)
			stmtCheckMail.close();
		if (stmtCheckMailSite != null)
			stmtCheckMailSite.close();
		if (stmtCheckMailSiteNoUser != null)
			stmtCheckMailSiteNoUser.close();
		if (stmtCheckMailPass != null)
			stmtCheckMailPass.close();

		if (stmtQQSite != null)
			stmtQQSite.close();
		if (stmtQQPass != null)
			stmtQQPass.close();
		if (stmtCheckQQSite != null)
			stmtCheckQQSite.close();
		if (stmtCheckQQSiteNoUser != null)
			stmtCheckQQSiteNoUser.close();
		if (stmtCheckQQPass != null)
			stmtCheckQQPass.close();

		if (conn != null)
			conn.close();
	}

	private static void loadDomains() throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("select id,domain from email_domain");
			rs = ps.executeQuery();
			while (rs.next()) {
				String domain = rs.getString("domain");
				int dId = rs.getInt("id");
				domains.put(domain, dId);
			}
		} catch (SQLException e) {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
		}
	}

	private static Integer getDomainId(String domain) {
		Integer ret = domains.get(domain);
		if (ret == null) {
			int success = 0;
			try {
				stmtDomain.setString(1, domain);
				success = stmtDomain.executeUpdate();
			} catch (SQLException e) {
				output("ERROR: " + e.getMessage());
			}

			if (success > 0) {
				domainInserted++;
				try {
					ResultSet rs = stmtDomain.getGeneratedKeys();
					if (rs.next()) {
						ret = rs.getInt(1);
					}
					domains.put(domain, ret);
				} catch (SQLException e) {
					output("ERROR: " + e.getMessage());
				}
			}
		} else {
			domainSkipped++;
		}

		return ret;
	}

	private static String getStats() throws Exception {
		return "mi=" + mailInserted + ", ms=" + mailSkipped + ", mr="
				+ conWrong + ", qi=" + qqInserted + ", di=" + domainInserted
				+ ", ds=" + domainSkipped + ", si=" + siteInserted + ", pi="
				+ passInserted;
	}

	private static void openLogFile() throws Exception {
		File file = new File("output" + System.currentTimeMillis() + ".txt");
		while (file.exists()) {
			Thread.sleep(100);
			file = new File("output" + System.currentTimeMillis() + ".txt");
		}
		writer = new PrintWriter(new FileWriter(file));
	}

	private static void updateCreateTime() {
		PreparedStatement ps = null;
		ResultSet rs = null;

		int max1 = 0;
		try {
			ps = conn.prepareStatement("select max(id) from email");
			rs = ps.executeQuery();
			if (rs.next()) {
				if (!rs.wasNull()) {
					max1 = rs.getInt(1);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		int max2 = 0;
		try {
			ps = conn.prepareStatement("select max(id) from email_domain");
			rs = ps.executeQuery();
			if (rs.next()) {
				if (!rs.wasNull()) {
					max2 = rs.getInt(1);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		int max3 = 0;
		try {
			ps = conn.prepareStatement("select max(id) from email_site");
			rs = ps.executeQuery();
			if (rs.next()) {
				if (!rs.wasNull()) {
					max3 = rs.getInt(1);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		int max4 = 0;
		try {
			ps = conn.prepareStatement("select max(id) from email_password");
			rs = ps.executeQuery();
			if (rs.next()) {
				if (!rs.wasNull()) {
					max4 = rs.getInt(1);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		int max5 = 0;
		try {
			ps = conn.prepareStatement("select max(id) from qq_site");
			rs = ps.executeQuery();
			if (rs.next()) {
				if (!rs.wasNull()) {
					max5 = rs.getInt(1);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		int max6 = 0;
		try {
			ps = conn.prepareStatement("select max(id) from qq_password");
			rs = ps.executeQuery();
			if (rs.next()) {
				if (!rs.wasNull()) {
					max6 = rs.getInt(1);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}

		try {
			ps = conn
					.prepareStatement("insert into create_time(time , email_start, domain_start, email_site_start, email_password_start, qq_site_start, qq_password_start)"
							+ " values (now(), ?, ?, ?, ?, ?, ?)");
			ps.setInt(1, max1);
			ps.setInt(2, max2);
			ps.setInt(3, max3);
			ps.setInt(4, max4);
			ps.setInt(5, max5);
			ps.setInt(6, max6);
			ps.executeUpdate();
		} catch (SQLException e) {
			output("ERROR: " + e.getMessage());
		}
	}

	public static void importMails() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			openLogFile();

			String dir = workDir;
			String work = dir + "/work";
			pos = 0;

			renewDBConn();
			loadDomains();
			updateCreateTime();

			while (true) {
				File[] files = new File(work).listFiles();
				if (files == null || files.length == 0) {
					boolean have = moveOneFile(dir, work);
					output("No work file found, Move candidate file");
					if (!have) {
						output("No Candidate Files");
						break;
					} else {
						pos = 0;
						savePosition(pos);
						continue;
					}
				} else {
					pos = loadPosition();
					output("Found File [" + files[0].toString()
							+ "], position [" + pos + "]");
				}

				FileInputStream fis = new FileInputStream(files[0]);
				fis.skip(pos);

				long c = 0;
				String line = null;
				while ((line = readLine(fis)) != null) {
					insertOne(line);
					c++;
					if ((c % batchSize) == 0) {
						output("" + c + "-" + (pos - 1) + " [" + getStats()
								+ "]");
						savePosition(pos - 1);
						closeDBConn();
						renewDBConn();
					}
				}

				if (line == null) {
					fis.close();
					while (files[0].delete()) {
						Thread.sleep(500);
					}
				}
			}

		} finally {
			closeDBConn();
		}
	}

	public static void main(String[] args) throws Exception {
		importMails();
	}
}
